** adding unemployment
import delimited "${path}/unemployment/DP_LIVE_28092023102442045.csv", varnames(1) clear 

keep if subject=="TOT"
keep location time value
keep if strpos(time, "-Q")

rename value unemployment_q
split time, p("-Q")
rename time1 year

tab year
rename time2 qrt
destring year qrt, replace
drop time 

replace qrt=5 if qrt==2
replace qrt=8 if qrt==3
replace qrt=10 if qrt==4

gen qdate = qofd(dofm(ym(year, qrt)))
format %tq qdate

kountry location, from(iso3c)
drop location
gen country_name=NAMES_STD
drop NAMES_STD
drop if year<2000 | year>2021
tab year
drop if missing(qrt)

keep country_name qdate unemployment_q
gen unemp_source="oecd"

preserve
use "${path}/unemployment/tipsun30.dta", clear

split time, p("Q")
rename time1 year
rename time2 qrt
destring year qrt, replace

replace qrt=5 if qrt==2
replace qrt=8 if qrt==3
replace qrt=10 if qrt==4

gen qdate = qofd(dofm(ym(year, qrt)))
format %tq qdate

drop if year<2000 | year>2021

drop time year qrt 
rename unemployment_q unemp2
rename country country_name
gen unemp_source="eurostat"
save "${path}/unemployment/tipsun30_parsed.dta", replace
restore

merge m:1 country_name qdate using "${path}/unemployment/tipsun30_parsed.dta" // countries that were not in the OECD data, from the Eurostat
drop _merge

replace unemployment_q=unemp2 if missing(unemployment_q)
drop unemp2

gen cntrystring = "AT" if country_name=="Austria"
replace cntrystring = "BE" if country_name=="Belgium"
replace cntrystring = "BG" if country_name=="Bulgaria"
replace cntrystring = "CY" if country_name=="Cyprus"
replace cntrystring = "CZ" if country_name=="Czech Republic"
replace cntrystring = "DK" if country_name=="Denmark"
replace cntrystring = "EE" if country_name=="Estonia"
replace cntrystring = "FI" if country_name=="Finland"
replace cntrystring = "FR" if country_name=="France"
replace cntrystring = "DE" if country_name=="Germany"
replace cntrystring = "GR" if country_name=="Greece"
replace cntrystring = "HU" if country_name=="Hungary"
replace cntrystring = "IS" if country_name=="Iceland"
replace cntrystring = "IE" if country_name=="Ireland"
replace cntrystring = "IT" if country_name=="Italy"
replace cntrystring = "LV" if country_name=="Latvia"
replace cntrystring = "LT" if country_name=="Lithuania"
replace cntrystring = "LU" if country_name=="Luxembourg"
replace cntrystring = "MT" if country_name=="Malta"
replace cntrystring = "NL" if country_name=="Netherlands"
replace cntrystring = "NO" if country_name=="Norway"
replace cntrystring = "PL" if country_name=="Poland"
replace cntrystring = "PT" if country_name=="Portugal"
replace cntrystring = "RO" if country_name=="Romania"
replace cntrystring = "SK" if inlist(country_name, "Slovak Republic", "Slovakia")
replace cntrystring = "SI" if country_name=="Slovenia"
replace cntrystring = "ES" if country_name=="Spain"
replace cntrystring = "SE" if country_name=="Sweden"
replace cntrystring = "CH" if country_name=="Switzerland"
replace cntrystring = "UK" if country_name=="United Kingdom"

tab country_name if missing(cntrystring)
drop if missing(cntrystring)
rename cntrystring iso2c

tab country_name if missing(iso2c)

drop country_name

save "${path}/unemployment/unemployment_cleaned.dta", replace

import delimited "${path}/unemployment/lms.csv", varnames(1) clear 

keep title v12 standardisedilounemploymentrates v27 

keep if strpos(title, "Q")

tab title if !missing(v12)
rename v12 iso2c_BG

tab title if !missing(standardisedilounemploymentrates)
rename standardisedilounemploymentrates iso2c_RO

tab title if !missing(v27)
rename v27 iso2c_CY

rename title time

split time, p(" Q")
rename time1 year
rename time2 qrt
destring year qrt, replace


replace qrt=5 if qrt==2
replace qrt=8 if qrt==3
replace qrt=10 if qrt==4

gen qdate = qofd(dofm(ym(year, qrt)))
format %tq qdate

drop time qrt year 

reshape long iso2c_, i(qdate) string

rename _j iso2c

rename iso2c_ unemp2

destring unemp2, replace

gen year=yofd(dofq( qdate ))

drop if year<2000 | year>2021
drop year
gen unemp_source="eurostat"

merge 1:1 iso2c qdate using "${path}/unemployment/unemployment_cleaned.dta"

replace unemployment_q=unemp2 if missing(unemployment_q)
drop unemp2
drop _merge

save "${path}/unemployment/unemployment_cleaned.dta", replace

// for CH before 2010

import delimited "${path}/unemployment/ds-x-03.03.01a.csv", varnames(1) clear 

drop valeursbrutes

rename valeursdésaisonnalisées unemp2

gen iso2c = "CH"

gen time = date(period, "DMY")
format time %td

gen qdate = qofd(time)
format %tq qdate

drop time period


gen year=yofd(dofq( qdate ))

drop if year<2000 | year>2021
drop year
gen unemp_source="eurostat"

merge 1:1 iso2c qdate using "${path}/unemployment/unemployment_cleaned.dta"

replace unemployment_q=unemp2 if missing(unemployment_q)
drop unemp2

bigtab qdate unemployment_q if iso2c=="CH"

drop _merge

save "${path}/unemployment/unemployment_cleaned.dta", replace


** adding unemployment
import delimited "${path}/unemployment/OECD_QNA_27092023190233692.csv", varnames(1) clear 

keep unit time value location flags

rename value gdp_per_capita
split time, p("-Q")
rename time1 year

tab year
rename time2 qrt
destring year qrt, replace
drop time 

replace qrt=5 if qrt==2
replace qrt=8 if qrt==3
replace qrt=10 if qrt==4

gen qdate = qofd(dofm(ym(year, qrt)))
format %tq qdate

kountry location, from(iso3c)
drop location
gen country_name=NAMES_STD
drop NAMES_STD
drop if year<2000 | year>2021
tab year

keep country_name qdate gdp_per_capita

gen cntrystring = "AT" if country_name=="Austria"
replace cntrystring = "BE" if country_name=="Belgium"
replace cntrystring = "BG" if country_name=="Bulgaria"
replace cntrystring = "CY" if country_name=="Cyprus"
replace cntrystring = "CZ" if country_name=="Czech Republic"
replace cntrystring = "DK" if country_name=="Denmark"
replace cntrystring = "EE" if country_name=="Estonia"
replace cntrystring = "FI" if country_name=="Finland"
replace cntrystring = "FR" if country_name=="France"
replace cntrystring = "DE" if country_name=="Germany"
replace cntrystring = "GR" if country_name=="Greece"
replace cntrystring = "HU" if country_name=="Hungary"
replace cntrystring = "IS" if country_name=="Iceland"
replace cntrystring = "IE" if country_name=="Ireland"
replace cntrystring = "IT" if country_name=="Italy"
replace cntrystring = "LV" if country_name=="Latvia"
replace cntrystring = "LT" if country_name=="Lithuania"
replace cntrystring = "LU" if country_name=="Luxembourg"
replace cntrystring = "MT" if country_name=="Malta"
replace cntrystring = "NL" if country_name=="Netherlands"
replace cntrystring = "NO" if country_name=="Norway"
replace cntrystring = "PL" if country_name=="Poland"
replace cntrystring = "PT" if country_name=="Portugal"
replace cntrystring = "RO" if country_name=="Romania"
replace cntrystring = "SK" if inlist(country_name, "Slovak Republic", "Slovakia")
replace cntrystring = "SI" if country_name=="Slovenia"
replace cntrystring = "ES" if country_name=="Spain"
replace cntrystring = "SE" if country_name=="Sweden"
replace cntrystring = "CH" if country_name=="Switzerland"
replace cntrystring = "UK" if country_name=="United Kingdom"

tab country_name if missing(cntrystring)
drop if missing(cntrystring)
rename cntrystring iso2c

tab country_name if missing(iso2c)

drop country_name

save "${path}/unemployment/gdp_per_capita_cleaned.dta", replace

import excel "${path}/unemployment/WEO_Data-2.xlsx", firstrow clear

keep AA AB F-S T U V-Z Country

rename F year_2000
rename G year_2001
rename H year_2002
rename I year_2003
rename J year_2004
rename K year_2005
rename L year_2006
rename M year_2007
rename N year_2008
rename O year_2009
rename P year_2010
rename Q year_2011
rename R year_2012
rename S year_2013
rename T year_2014
rename U year_2015
rename V year_2016
rename W year_2017
rename X year_2018
rename Y year_2019
rename Z year_2020
rename AA year_2021
rename AB year_2022

reshape long year_, i(Country) string

rename year_ gdp_per_capita_cy
rename _j year 

drop Country
gen iso2c="CY"

drop if missing(gdp_per_capita_cy)

replace gdp_per_capita_cy = subinstr(gdp_per_capita_cy,",","",.)

destring gdp_per_capita_cy, replace
destring year, replace

save "${path}/unemployment/cy_gdp_per_capita.dta", replace

